Business Rules Compatibility

There are some business rules that are not compatible with Smart Integration Connector. If you attempt certain rules, you will run into the following error: This BRAPI is not compatible with Smart Integration Connector. Refer to Smart Integration Connector Remote BRs.

The following business rules are not compatible with Smart Integration Connector:

BRApi.Database.SaveCustomDataTable

Although, this business rule is not supported, the functionality can be achieved through a remote business rule. You can call this business rule using BRApi.Utilities.ExecRemoteGatewayBusinessRule.

Here is the rule in C#:

Copy 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
 
namespace OneStream.BusinessRule.SmartIntegrationFunction.SaveCustomDataTable
{
    public class MainClass
    {
        public void RunOperation()
        {
            var tableName = "";         // Enter the name of the table to update
            var connectionName = "";     // Enter the name of the configured database connection
            var connString = OneStreamGatewayService.APILibrary.GetRemoteDataSourceConnection(connectionName);
            var dataTable = new DataTable();
            using (var connection = new SqlConnection(connString))
            {
                connection.Open();
                var sql = $"SELECT * FROM {tableName}";
                var cmd = new SqlCommand(sql, connection);
                var adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;    
                var commandBuilder = new SqlCommandBuilder(adapter);
                adapter.Fill(dataTable);
                // Add logic here to update values in DataTable                
                // Update database with changes to the DataTable
                adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                adapter.Update(dataTable);
            }
        }
    }
}

Here is the same rule VB:

Copy 
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Namespace OneStream.BusinessRule.SmartIntegrationFunction.SaveCustomDataTableVB
    Public Class MainClass
        Public Sub RunOperation()
         Dim tableName = ""            ' Enter the name of the table to update
         Dim connectionName = ""        ' Enter the name of the configured database connection
         Dim connString = OneStreamGatewayService.APILibrary.GetRemoteDataSourceConnection(connectionName)
         Dim dataTable = New DataTable()
         Using connection = New SqlConnection(connString)
         connection.Open()
         Dim sql = $"SELECT * FROM {tableName}"
         Dim cmd = New SqlCommand(sql, connection)
         Dim adapter = New SqlDataAdapter()
         adapter.SelectCommand = cmd
         Dim commandBuilder = New SqlCommandBuilder(adapter)
         adapter.Fill(dataTable)
                ' Add logic here to update values in DataTable
                ' Update database with changes to the DataTable
         adapter.UpdateCommand = commandBuilder.GetUpdateCommand()
         adapter.Update(dataTable)
         End Using
        End Sub
    End Class
End Namespace

BRApi.Database.InsertOrUpdateRow
BRApi.Database.DeleteRows

Although, these business rules are not supported, inserting, deleting and updating rows can be accomplished through the same remote business rule referenced in BRApi.Database.SaveCustomDataTable. You can call this business rule using BRApi.Utilities.ExecRemoteGatewayBusinessRule. You will insert your logic at the specific comment in the remote business rule.

BRApi.Database.ExecuteActionQuery

ExecuteActionQuery does not support returning a scalar when used as a remote business rule. A sample workaround is provided below:

Copy 
Else If args.DrillBackType.NameAndDescription.Name.Equals("ScalarQuery", StringComparison.InvariantCultureIgnoreCase) Then    
                        
                        Dim intResult As Int32 = 0
                        brapi.ErrorLog.Logmessage(si, "Entered GetData")
                        Dim sqlExecStatement As String = "Select count(*) FROM InvoiceMaterialDetail"
                        brapi.ErrorLog.LogMessage(si, "statement = " & sqlExecStatement)
                            Using dbAXConn As DbConnInfo = BRApi.Database.CreateRelayDbConnInfo(si, "jl_SQL_Gateway_Connection")
                                Using sicConnection As DbConnection = dbAXConn.GetConn()
                                    Dim sprocCmd = sicConnection.CreateCommand()
                                    sproccmd.commandtext = sqlExecStatement
                                    intResult = sprocCmd.ExecuteScalar  ' Should be non-zero
                                    sicConnection.Close()                                    
                                End Using
                                dbAXConn.Close()
                            End Using             
                            
                        Dim drillBackInfo As New DrillBackResultInfo
                        drillbackinfo.TextMessage = intResult
                        drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.TextMessage                                    
                        Return drillBackInfo

SQL Bulk Copy

Use of the SQL Bulk Copy class is not supported to copy to and from databases accessed over Smart Integration Connector. Currently, there is not a workaround available.

SQL Transactions

Use of the SqlTransaction class is currently only supported in Smart Integration Connector Functions / remote business rules.